﻿<?php

class SyncODBCTask extends sfBaseTask
{
  protected function configure()
  {
    // // add your own arguments here
    // $this->addArguments(array(
    //   new sfCommandArgument('my_arg', sfCommandArgument::REQUIRED, 'My argument'),
    // ));

    $this->addOptions(array(
      new sfCommandOption('application', null, sfCommandOption::PARAMETER_REQUIRED, 'admin'),
      new sfCommandOption('env', null, sfCommandOption::PARAMETER_REQUIRED, 'The environment', 'dev'),
      new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'The connection name', 'doctrine'),
      // add your own options here
    ));

    $this->namespace        = 'sync';
    $this->name             = 'ODBC';
    $this->briefDescription = '';
    $this->detailedDescription = <<<EOF
The [SyncODBC|INFO] task does things.
Call it with:

  [php symfony SyncODBC|INFO]
EOF;
  }

  protected function execute($arguments = array(), $options = array())
  {
    // initialize the database connection
    $databaseManager = new sfDatabaseManager($this->configuration);
    $connection = $databaseManager->getDatabase($options['connection'])->getConnection();
    
    sfContext::createInstance(sfProjectConfiguration::getApplicationConfiguration('admin','dev', true));
    // add your code here
    ini_set('memory_limit', '-1');
    $odbc_conn = $this->connectToODBC();
    
    $this->syncSectors($odbc_conn);
    $this->syncSchools($odbc_conn);
    $this->syncTeachers($odbc_conn);
    
    
    // at last close the ODBC connection
    odbc_close($odbc_conn);
  }
  
  /**
   * initiate a connection to the access database
   * @return type 
   */
  protected function connectToODBC(){
      
      echo 'Connecting to access DB ...'.chr(10);
      $dns = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" . str_replace("/", "\\", sfConfig::get('sf_data_dir'). "/teacher.mdb");      
      echo $dns.chr(10);
      echo '-----------------'.chr(10);
      $odbc_conn = odbc_connect($dns, sfConfig::get('app_odbc_settings_username'), sfConfig::get('app_odbc_settings_password'));
      if(!$odbc_conn){
          die('cannot connect to Access Database...');
      }else{
          echo 'Connected successfully to Access Database'.chr(10);
      }
      
      return $odbc_conn;
  }

  protected function syncSectors($odbc_conn){
      echo '------------'.chr(10);
      echo 'Begin synchronizing Sectors data'.chr(10);
      $sql = "SELECT
          SectID,
          Sector
          FROM
          TSector
    "; 
      
      $rs = odbc_exec($odbc_conn, $sql);
      while(odbc_fetch_row($rs)){
          $intg_id = odbc_result($rs, 'SectID');
          
          $org_sector_obj = CodeSectorTable::getInstance()->retrieveByIntgId($intg_id);
          if(!$org_sector_obj){
              $org_sector_obj = new CodeSector();
              $org_sector_obj['intg_id'] = $intg_id;
          }
          $org_sector_obj['intg_id'] = odbc_result($rs, 'SectID');
          $org_sector_obj['name'] = iconv("CP1256", "UTF-8", odbc_result($rs, 'Sector'));
          
          $org_sector_obj->save();
          echo "Sector #{$intg_id} has been synched successfully.".chr(10);
      }
  }
  
  protected function syncSchools($odbc_conn){
      echo '------------'.chr(10);
      echo 'Begin synchronizing schools data'.chr(10);
      $sql = "SELECT
          SchoolID,
          SectID,
          School,
          LevelNo,
          FDate,
          Build,
          SchType,
          dismiss,
          office,
          EMail,
          Phone,
          Liver,
          street
          FROM
          TSchools
    "; //Street, area to be placed in query after decision of arabic fields
      
      $rs = odbc_exec($odbc_conn, $sql);
      while(odbc_fetch_row($rs)){
          $intg_id = odbc_result($rs, 'SchoolID');
          
          $org_school_obj = SchoolTable::getInstance()->retrieveByIntgId($intg_id);
          if(!$org_school_obj){
              $org_school_obj = new School();
              $org_school_obj['intg_id'] = $intg_id;
          }
          $org_school_obj['sector_intg_id'] = odbc_result($rs, 'SectID');
          $org_school_obj['name'] = iconv("CP1256", "UTF-8", odbc_result($rs, 'School'));
          
          $org_school_obj['level']   = odbc_result($rs, 'LevelNo');
          $org_school_obj['fdate']   = odbc_result($rs, 'FDate');
          $org_school_obj['build']   = odbc_result($rs, 'Build');
          $org_school_obj['type']    = odbc_result($rs, 'SchType');
          $org_school_obj['dismiss'] = odbc_result($rs, 'dismiss');
          $org_school_obj['office_number'] = odbc_result($rs, 'office');
          $org_school_obj['email']   = odbc_result($rs, 'EMail');
          $org_school_obj['phone']   = odbc_result($rs, 'Phone');
          $org_school_obj['region']   = iconv("CP1256", "UTF-8", odbc_result($rs, 'Liver'));
          $org_school_obj['street']   = iconv("CP1256", "UTF-8", odbc_result($rs, 'street'));
          
          $org_school_obj->save();
          echo "School #{$intg_id} has been synched successfully.".chr(10);
      }
  }
  
  public function syncTeachers($odbc_conn){
      echo '------------'.chr(10);
      echo 'Begin synchronizing Teachers data'.chr(10);
      $sql = "SELECT
          Identity,
          Identity2,
          SchID,
          SubID,
          name1,
          name2,
          name3,
          FName,
          NatID,
          QID,
          QDatey,
          QDatem,
          QDated,
          University,
          SpecialID,
          WID,
          PreSchool,
          WorkDatey,
          WorkDatem,
          WorkDated,
          JobID,
          BirthDatem,
          BirthDatey,
          BirthDated,
          `level`,
          `degree`,
          EmpStatus,
          mopaul,
          `phone`
          FROM
          TImployees
    "; 
      
      $rs = odbc_exec($odbc_conn, $sql);
      while(odbc_fetch_row($rs)){
          $intg_id = odbc_result($rs, 'Identity');		  
          
          $teacher_obj = TeacherTable::getInstance()->retrieveByIntgId($intg_id);
          if(!$teacher_obj){
              $teacher_obj = new Teacher();
              $teacher_obj['intg_identity'] = $intg_id;
          }else{
			continue; // skip teacher that are already stored
		  }
          
          $teacher_obj['intg_identity2'] = odbc_result($rs, 'Identity2');
          
          $school_intg_id = odbc_result($rs, 'SchID');
          $teacher_obj['school_intg_id'] = $school_intg_id;
          
          $teacher_obj['sub_id']         = odbc_result($rs, 'SubID');
          $teacher_obj['f_name']         = iconv("CP1256", "UTF-8", odbc_result($rs, 'name1'));
          $teacher_obj['s_name']         = iconv("CP1256", "UTF-8", odbc_result($rs, 'name2'));
          $teacher_obj['t_name']         = iconv("CP1256", "UTF-8", odbc_result($rs, 'name3'));
          $teacher_obj['l_name']         = iconv("CP1256", "UTF-8", odbc_result($rs, 'FName'));
          $teacher_obj['nat_id']         = odbc_result($rs, 'NatID');
          $teacher_obj['q_id']           = odbc_result($rs, 'QID');
          $teacher_obj['is_manager']     = (odbc_result($rs, 'WID')==1?1:0);

          if( (strpos($school_intg_id, '999') !== false) &&
              strlen($school_intg_id) == 8 ){
              $teacher_obj['is_trainer'] = 1;
          }          
          $q_date_d = odbc_result($rs, 'QDated')?odbc_result($rs, 'QDated'):1;
          $q_date_m = odbc_result($rs, 'QDatem')?odbc_result($rs, 'QDatem'):1;
          $q_date_y = odbc_result($rs, 'QDatey');
          
          $teacher_obj['q_date']         = $q_date_y.'-'.$q_date_m.'-'.$q_date_d;
          $teacher_obj['university']     = odbc_result($rs, 'University');
          $teacher_obj['special_id']     = odbc_result($rs, 'SpecialID');
          $teacher_obj['pre_school']     = odbc_result($rs, 'PreSchool');
          
          $w_date_d = odbc_result($rs, 'WorkDated')?odbc_result($rs, 'WorkDated'):1;
          $w_date_m = odbc_result($rs, 'WorkDatem')?odbc_result($rs, 'WorkDatem'):1;
          $w_date_y = odbc_result($rs, 'WorkDatey');
          
          $teacher_obj['work_date'] = $w_date_y.'-'.$w_date_m.'-'.$w_date_d;
          $teacher_obj['job_id']    = odbc_result($rs, 'JobID');
          
          $b_date_d = odbc_result($rs, 'BirthDated')?odbc_result($rs, 'BirthDated'):1;
          $b_date_m = odbc_result($rs, 'BirthDatem')?odbc_result($rs, 'BirthDatem'):1;
          $b_date_y = odbc_result($rs, 'BirthDatey');
          
          $teacher_obj['birth_date'] = $b_date_y.'-'.$b_date_m.'-'.$b_date_d;
          
          $teacher_obj['level']      = odbc_result($rs, 'level');
          $teacher_obj['degree']     = odbc_result($rs, 'degree');
          $teacher_obj['emp_status'] = odbc_result($rs, 'EmpStatus');
          $teacher_obj['mobile']     = odbc_result($rs, 'mopaul');
          $teacher_obj['phone']      = odbc_result($rs, 'phone');
          
          $teacher_obj->save();
          echo "Teacher #{$intg_id} has been synched successfully".chr(10);
      }
  }
  
}
